package net.huashitong.Indicators.dao;

import java.text.ParseException;
import java.util.List;
import java.util.Map;

import com.sirdc.modules.utils.ObjectUtils;
import net.huashitong.Indicators.entity.IndicatorBreakdown;
import net.huashitong.Indicators.filter.IndicatorBreakdownFilter;
import net.huashitong.Indicators.filter.IndicatorRank;
import net.huashitong.Indicators.utils.DateForIndUtils;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.core.filter.BaseFilter;
import com.sirdc.modules.utils.StringUtils;

@Repository
public class IndicatorBreakdownDao extends StringDao<IndicatorBreakdown> {

    public List<IndicatorBreakdown> queryByCuMonthAndArea(
            String currentMonth, String area) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", currentMonth);
        query.append(" and obj.area = :area").setParam("area", area);
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryByCuMonthAndArea(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        query.append(" and obj.industryId = :industryId order by cast(obj.cumulative as int) desc").setParam("industryId", filter.getIndustryId());
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryByCuMonthAndAreaOR(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        query.append(" and obj.industryId = :industryId order by cast(obj.ratio as int) desc").setParam("industryId", filter.getIndustryId());
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryDevelopmentSituation(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        String total = dealTime(filter.getCurrentMonth());
        if ("03".contains(filter.getCurrentMonth())) {
            query.append(" and obj.date >= :dateS").setParam("dateS", filter.getCurrentMonth().substring(0, 4));
        } else {
            String lastTotal = lastDealTime(filter.getCurrentMonth());
            query.append(" and obj.date >= :dateS").setParam("dateS", lastTotal);
        }
        query.append(" and obj.date <= :date").setParam("date", total);
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryMonthDevelopmentSituation(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        if ("03".contains(filter.getCurrentMonth())) {
            query.append(" and obj.currentMonth >= :currentMonthS").setParam("currentMonthS", filter.getCurrentMonth().substring(0, 4));
        } else {
            String lastTotal = lastMonthTime(filter.getCurrentMonth());
            query.append(" and obj.currentMonth >= :currentMonthS").setParam("currentMonthS", lastTotal);
        }
        query.append(" and obj.currentMonth <= :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        return query.listResult();
    }

    public String dealTime(String currentMonth) {
        String year = currentMonth.substring(0, 4);
        String month = currentMonth.substring(4);
        String total = year + "01-" + month;
        return total;
    }

    public static String lastDealTime(String currentMonth) {
        String year = currentMonth.substring(0, 4);
        String month = currentMonth.substring(4);
        int iyear = Integer.parseInt(month);
        iyear = iyear - 3;
        String total = "";
        if (iyear > 9) {
            total = year + "01-" + iyear;
        } else {
            total = year + "01-0" + iyear;
        }

        return total;
    }

    public static String lastMonthTime(String currentMonth) {
        String year = currentMonth.substring(0, 4);
        String month = currentMonth.substring(4);
        int iyear = Integer.parseInt(month);
        iyear = iyear - 1;
        String total = "";
        if (iyear > 9) {
            total = year + iyear;
        } else {
            total = year + "0" + iyear;
        }

        return total;
    }

    public List<IndicatorBreakdown> queryAbsoluteValue(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.date like :date").setParam("date", StringUtils.getLikewords(filter.getDate()));
        query.append(" and obj.date <= :dateSec").setParam("dateSec", DateForIndUtils.getMonth(filter.getCurrentMonth()));
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        query.getOrders().put("date", BaseFilter.DESC);
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryMonthAbsolute(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth like :currentMonth").setParam("currentMonth", StringUtils.getLikewords(filter.getDate()));
        try {
            query.append(" and obj.currentMonth < :month").setParam("month", DateForIndUtils.getNextMonthDate(filter.getCurrentMonth(), 1));
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        query.getOrders().put("currentMonth", BaseFilter.DESC);
        return query.listResult();
    }

    public List<Map<String, Object>> queryAll(String date, String industryName, String area) {
		/*String hql = "select c.sys_id as industry_id,a.ratio,a.industry_name,a.cumulative,"
				+ "(select COUNT(*) from t_gis_indicator_breakdown b where cast(b.ratio as DECIMAL(10,4)) >= cast(a.ratio as DECIMAL(10,4)) and b.date= ? and b.industry_name= ? and b.area!='福州市') rank, "
				+ " c.county_increase,c.unit "
				+ "FROM t_gis_indicator_breakdown AS a left join t_gis_tertiary_industry c on c.indicator_name = a.industry_name where a.date=?"
				+ " and a.industry_name=? and a.area = ?  ORDER BY a.ratio";*/
        String hql =
                "select  a.industry_id,a.ratio,a.industry_name,a.cumulative,a.rank, b.county_increase ,b.unit  from ( " +
                        "select t1.industry_id, t1.ratio,t1.area ,t1.industry_name,t1.cumulative,@rank\\:=@rank+1," +
                        "@last_rank\\:=case " +
                        "when @last_ratio=t1.ratio " +
                        "then @last_rank " +
                        "when @last_ratio\\:=t1.ratio " +
                        "then @rank " +
                        "end  as rank " +
                        "from (select industry_id, ratio,area,cumulative,industry_name  from t_gis_indicator_breakdown where date =? " +
                        "AND industry_name =? " +
                        "and area<>'福州市'  ORDER BY ratio+0 desc ) t1, " +
                        "(select @rank\\:=0,@last_ratio\\:=null ,@last_rank\\:=0) t2 ) a,t_gis_tertiary_industry b " +
                        "where a.area =? and a.industry_id = b.sys_id ";


        SQLQuery query = getSession().createSQLQuery(hql);
        query.setParameter(0, date);
        query.setParameter(1, industryName);

        query.setParameter(2, area);

        List list = query.list();
        return list;
    }

    public List<IndicatorBreakdown> queryShopping(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.area in (:areas)").setParamList("areas", filter.getAreas());
        query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        try {
            query.append(" and (obj.date like :dateSe or obj.date like :dateTh)")
                    .setParam("dateSe", StringUtils.getLikewords(DateForIndUtils.getNextYearDate(filter.getDate().substring(0, 4), -1)))
                    .setParam("dateTh", StringUtils.getLikewords(DateForIndUtils.getNextYearDate(filter.getDate().substring(0, 4), -2)));
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        query.append(" and obj.date not like '%%-03' ORDER BY obj.date DESC");
        return query.listResult();
    }

    public List<IndicatorBreakdown> queryShoppingInd(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.area in (:areas)").setParamList("areas", filter.getAreas());
        query.append(" and obj.industryName in(:industryNames)").setParamList("industryNames", filter.getIndustryNames());
        query.append(" and obj.date = :date ORDER BY obj.area").setParam("date", filter.getDate());
        return query.listResult();
    }

    public List<String> queryCountyRankArea(IndicatorBreakdownFilter filter) {
        String hql = "select area from IndicatorBreakdown obj";
        GenericQuery query = create(hql);
        query.append(" where obj.currentMonth = :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        query.append(" and obj.industryId = :industryId order by cast(sysId as int) desc").setParam("industryId", filter.getIndustryId());
        return query.listResult();
    }

    public List<String> queryAllDateTimes() {
        String hql = "select currentMonth from IndicatorBreakdown obj";
        GenericQuery query = create(hql);
        query.append(" group by currentMonth order by cast(currentMonth as int) desc");
        return query.listResult();
    }

    public IndicatorBreakdown queryMonthValue(IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        if (StringUtils.isNotBlank(filter.getIndustryId())) {
            query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        }
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        return query.singleResult();
    }

    //根据指标id、当前月、地区来查询
    public List<IndicatorBreakdown> queryMonthValueSec(
            IndicatorBreakdownFilter filter) {
        GenericQuery query = createQuery("obj");
        System.out.print("时间=" + filter.getDate());
        System.out.print("指标id=" + filter.getIndustryId());

        System.out.print("当前月=" + filter.getCurrentMonth());
        System.out.print("地区来查询=" + filter.getArea());
        if (StringUtils.isNotBlank(filter.getDate())) {
            query.append(" and obj.currentMonth = :date").setParam("date", filter.getDate());
        }
        if (StringUtils.isNotBlank(filter.getCurrentMonth())) {
            query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", filter.getCurrentMonth());
        }
        if (StringUtils.isNotBlank(filter.getIndustryId())) {
            query.append(" and obj.industryId = :industryId").setParam("industryId", filter.getIndustryId());
        }
        query.append(" and obj.area = :area").setParam("area", filter.getArea());
        query.setOrder("industryId", BaseFilter.ASC);
        return query.listResult();
    }

    //根据 指标编码，时间，地区，获取指标数值
    public IndicatorBreakdown queryByDateAndId(
            IndicatorBreakdown indicatorBreakdown) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(indicatorBreakdown.getDate())) {
            query.append(" and obj.date = :date").setParam("date", indicatorBreakdown.getDate());
        }
        if (StringUtils.isNotBlank(indicatorBreakdown.getCurrentMonth())) {
            query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", indicatorBreakdown.getCurrentMonth());
        }
        query.append(" and obj.industryId = :industryId").setParam("industryId", indicatorBreakdown.getIndustryId());
        query.append(" and obj.area = :area").setParam("area", indicatorBreakdown.getArea());
        return query.singleResult();
    }

    public List queryAllSec(String date, String industryName, String area) {
        String hql = "select c.sys_id as industry_id,a.ratio,a.industry_name,a.cumulative,"
                + "(select COUNT(*) from t_gis_indicator_breakdown b where cast(b.cumulative as DECIMAL(10,4)) >= cast(a.cumulative as DECIMAL(10,4)) and b.date= ? and b.industry_name= ? and b.area!='福州市' ORDER BY b.cumulative+0 DESC) rank, "
                + " c.county_increase,c.unit "
                + "FROM t_gis_indicator_breakdown AS a left join t_gis_tertiary_industry c on c.indicator_name = a.industry_name where a.date=?"
                + " and a.industry_name=? and a.area = ?";
        SQLQuery query = getSession().createSQLQuery(hql);
        query.setParameter(0, date);
        query.setParameter(1, industryName);
        query.setParameter(2, date);
        query.setParameter(3, industryName);
        query.setParameter(4, area);

        List list = query.list();
        return list;
    }

    public List<IndicatorBreakdown> queryByIndustryId(String industryId, String currentMonth) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", currentMonth);
        query.append(" and obj.industryId = :industryId order by cast(obj.ratio as int) desc").setParam("industryId", industryId);
        return query.listResult();
    }

    public IndicatorBreakdown queryIndicatorBreakdownByIndustryId(String industryId, String currentMonth, String area) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.currentMonth = :currentMonth").setParam("currentMonth", currentMonth);
        query.append(" and obj.area = :area").setParam("area", area);
        query.append(" and obj.industryId = :industryId").setParam("industryId", industryId);
        return query.firstResult();
    }

    public String getNewDate() {
        GenericQuery query = createQuery("obj");
        query.setOrder("date", BaseFilter.DESC);
        IndicatorBreakdown indicatorBreakdown = query.firstResult();
        if (ObjectUtils.isBlank(indicatorBreakdown)) {
            return "201706";
        }
        return indicatorBreakdown.getCurrentMonth();
    }

    public List<IndicatorBreakdown> queryIndicatorBreakdowns(String area, String industryId, String date) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(area)) {
            query.append(" and obj.area = :area").setParam("area", area);
        }
        if (StringUtils.isNotBlank(industryId)) {
            query.append(" and obj.industryId = :industryId").setParam("industryId", industryId);
        }
        if (StringUtils.isNotBlank(date)) {
            query.append(" and obj.currentMonth = :date").setParam("date", date);
        }
        query.setOrder("currentMonth", BaseFilter.DESC);
        return query.listResult();
    }

    /**
     * 获取增幅排名
     *
     * @return
     */
    public List<IndicatorRank> getRatioRank(String industryId, String currentMonth) {
        String hql =
                "select area,current_month as currentMonth,date,industry_id as industryId," +
                        "industry_name as industryName,ratio,cumulative ," +
                        "(select count(distinct ratio) FROM " +
                        "(select * from t_gis_indicator_breakdown where industry_id = '" + industryId + "' and current_month = '" + currentMonth + "') as b " +
                        "where a.ratio+0 < b.ratio+0 )+1 as rank " +
                        " from " +
                        " (select * from t_gis_indicator_breakdown where industry_id = '" + industryId + "' and current_month = '" + currentMonth + "') as a " +
                        "order by rank desc;";
        SQLQuery query = getSession().createSQLQuery(hql);
        query.setResultTransformer(Transformers.aliasToBean(IndicatorRank.class));
        List<IndicatorRank> list = (List<IndicatorRank>) query.list();
        return list;
    }


    /**
     * 根据指标Id 获取当前指标的最新月份
     *
     * @param IndicatorId
     * @return
     */
    public IndicatorBreakdown getIndicatorDateTime(String IndicatorId) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.industryId = :industryId").setParam("industryId", IndicatorId);
        query.setOrder("currentMonth", BaseFilter.DESC);
        query.setMaxResults(1);
        return query.singleResult();
    }

    public IndicatorBreakdown getIndicatorDateTimeByName(String IndicatorName) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.industryName = :industryName").setParam("industryName", IndicatorName);
        query.setOrder("currentMonth", BaseFilter.DESC);
        query.setMaxResults(1);
        return query.singleResult();
    }

    public List<IndicatorBreakdown> queryAllDataByYear(String year, String area) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(year)) {
            query.append(" and obj.currentMonth like:currentMonth");
            query.setParam("currentMonth", year + "%");
        }
        if (StringUtils.isNotBlank(area)) {
            query.append(" and obj.area like :area").setParam("area", StringUtils.getLikewords(area));
        }
        query.setOrder("currentMonth", BaseFilter.ASC);
        return query.listResult();
    }

}
